using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using System.Net.Mail;
using System.Data.Common;
using System.Web.UI.DataVisualization.Charting;

public partial class DatabaseDetail : System.Web.UI.Page
{
    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.btnDelete.Attributes.Add("OnCLick", "return confirm('Are you sure you want to delete this database from the repository?');");
            
            if (Convert.ToBoolean(Session["sAdministrator"].ToString()) == false)
            {
                this.btnDelete.Visible = false;
                this.btnUpdate.Visible = false;
                this.btnAddNote.Visible = false;
                this.lnkSendEMail.Visible = false;
                this.gv.Enabled = false;                
                this.txtPrimaryContactEMail.Enabled = false;
                this.chkIsAudited.Enabled = false;
                this.chkIsSensitive.Enabled = false;                
            }
            else //Administrator
            {
                this.chkIsSensitive.Visible = true;
                this.chkIsAudited.Visible = true;
            }
                         
            BindData();
            this.txtBackupFrequencyFULL.Style.Add("text-align", "center");
            this.txtBackupFrequencyDIFF.Style.Add("text-align", "center");
            this.txtBackupFrequencyLOG.Style.Add("text-align", "center");
            this.lnkSendEMail.NavigateUrl = @"~/SendEMail.aspx?TO=" + this.txtPrimaryContactEMail.Text.ToString() + "&TYPE=DATABASE&Instance=" + this.lblInstanceName.Text.ToString() + "&Database="+this.lblDatabaseName.Text.ToString();
        }
    }
    private void BindData()
    {
        DbCommand dbCommand1 = db.GetSqlStringCommand("SELECT d.* FROM [Database] d Where d.InstanceName ='" + Request.QueryString["InstanceName"].ToString() + "' and d.DatabaseName='" + Request.QueryString["DatabaseName"].ToString() + "'");
        DataSet ds1 = db.ExecuteDataSet(dbCommand1);
        DataRow dr1 = ds1.Tables[0].Rows[0];

        this.chkIsAudited.Checked = Convert.ToBoolean(dr1["isAudited"].ToString());
        this.chkIsSensitive.Checked = Convert.ToBoolean(dr1["isSensitive"].ToString());

        this.lblInstanceName.Text = dr1["InstanceName"].ToString();
        this.lblDatabaseName.Text = dr1["DatabaseName"].ToString();
        this.txtPrimaryContactEMail.Text = dr1["PrimaryContactEMail"].ToString();
        this.lblTotalSizeMB.Text = dr1["TotalSizeMB"].ToString();
        this.lblDataSizeMB.Text = dr1["DataSizeMB"].ToString();        
        this.lblLogSizeMB.Text = dr1["LogSizeMB"].ToString();        
        this.lblLastBackupFULL.Text = dr1["LastBackupFULL"].ToString();
        this.lblLastBackupDIFF.Text = dr1["LastBackupDIFF"].ToString();
        this.lblLastBackupLOG.Text = dr1["LastBackupLOG"].ToString();
        this.txtBackupFrequencyFULL.Text = dr1["BackupFrequencyFULL"].ToString();
        this.txtBackupFrequencyDIFF.Text = dr1["BackupFrequencyDIFF"].ToString();
        this.txtBackupFrequencyLOG.Text = dr1["BackupFrequencyLOG"].ToString();
        this.lblRecoveryModel.Text = dr1["RecoveryModel"].ToString();
        this.lblCompatibilityLevel.Text = dr1["CompatibilityLevel"].ToString();


        //Notes
        //string sqlQuery = "SELECT InstanceName, DatabaseName,Note,CreatedBy,CreateDate FROM DatabaseNote Where DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "'";
        //DataSet ds = db.ExecuteDataSet(CommandType.Text, sqlQuery);
        //gv.DataSource = ds;
        //gv.DataBind();

        //DatabaseTrending Charts
        string qChtYear = "DECLARE @t TABLE (ByYear VARCHAR(10),TotalSizeMB INT,DataSizeMB INT,LogSizeMB INT,DataSpaceUsedMB INT) " +
                          "INSERT INTO @t SELECT '01', TotalSizeMBYear01,DataSizeMBYear01,LogSizeMBYear01,DataSpaceUsedMBYear01 FROM [Database]  WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                          "INSERT INTO @t SELECT '02', TotalSizeMBYear02,DataSizeMBYear02,LogSizeMBYear02,DataSpaceUsedMBYear02 FROM [Database]  WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                          "INSERT INTO @t SELECT '03', TotalSizeMBYear03,DataSizeMBYear03,LogSizeMBYear03,DataSpaceUsedMBYear03 FROM [Database]  WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                          "INSERT INTO @t SELECT '04', TotalSizeMBYear04,DataSizeMBYear04,LogSizeMBYear04,DataSpaceUsedMBYear04 FROM [Database]  WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                          "SELECT * FROM @t ORDER BY ByYear DESC";
        
        DataSet dsChtYear = db.ExecuteDataSet(CommandType.Text, qChtYear);
        chtYear.DataSource = dsChtYear;
        
        chtYear.Titles.Add("Last 4 Years");
        
        chtYear.Series.Add("TotalSizeMB");
        chtYear.Series.Add("DataSizeMB");
        chtYear.Series.Add("LogSizeMB");
        chtYear.Series.Add("DataSpaceUsedMB");

        chtYear.Series["TotalSizeMB"].ChartType = SeriesChartType.Line;
        chtYear.Series["TotalSizeMB"].BorderWidth = 2;
        chtYear.Series["TotalSizeMB"].Color = System.Drawing.Color.Red;
        chtYear.Series["DataSizeMB"].ChartType = SeriesChartType.Line;
        chtYear.Series["DataSizeMB"].BorderWidth = 2;
        chtYear.Series["DataSizeMB"].Color = System.Drawing.Color.Blue;
        chtYear.Series["LogSizeMB"].ChartType = SeriesChartType.Line;
        chtYear.Series["LogSizeMB"].BorderWidth = 2;
        chtYear.Series["LogSizeMB"].Color = System.Drawing.Color.Green;
        chtYear.Series["DataSpaceUsedMB"].ChartType = SeriesChartType.Line;
        chtYear.Series["DataSpaceUsedMB"].BorderWidth = 2;
        chtYear.Series["DataSpaceUsedMB"].Color = System.Drawing.Color.Purple;
        
        chtYear.Series["TotalSizeMB"].XValueMember = "ByYear";
        chtYear.Series["TotalSizeMB"].YValueMembers = "TotalSizeMB";
        chtYear.Series["DataSizeMB"].XValueMember = "ByYear";
        chtYear.Series["DataSizeMB"].YValueMembers = "DataSizeMB";
        chtYear.Series["LogSizeMB"].XValueMember = "ByYear";
        chtYear.Series["LogSizeMB"].YValueMembers = "LogSizeMB";
        chtYear.Series["DataSpaceUsedMB"].XValueMember = "ByYear";
        chtYear.Series["DataSpaceUsedMB"].YValueMembers = "DataSpaceUsedMB";
        chtYear.DataBind();

        //Month
        string qChtMonth = "DECLARE @t TABLE (ByMonth VARCHAR(10),TotalSizeMB INT,DataSizeMB INT,LogSizeMB INT,DataSpaceUsedMB INT) " +
                           "INSERT INTO @t SELECT '01', TotalSizeMBMonth01,DataSizeMBMonth01,LogSizeMBMonth01,DataSpaceUsedMBMonth01 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '02', TotalSizeMBMonth02,DataSizeMBMonth02,LogSizeMBMonth02,DataSpaceUsedMBMonth02 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '03', TotalSizeMBMonth03,DataSizeMBMonth03,LogSizeMBMonth03,DataSpaceUsedMBMonth03 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '04', TotalSizeMBMonth04,DataSizeMBMonth04,LogSizeMBMonth04,DataSpaceUsedMBMonth04 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '05', TotalSizeMBMonth05,DataSizeMBMonth05,LogSizeMBMonth05,DataSpaceUsedMBMonth05 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '06', TotalSizeMBMonth06,DataSizeMBMonth06,LogSizeMBMonth06,DataSpaceUsedMBMonth06 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '07', TotalSizeMBMonth07,DataSizeMBMonth07,LogSizeMBMonth07,DataSpaceUsedMBMonth07 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '08', TotalSizeMBMonth08,DataSizeMBMonth08,LogSizeMBMonth08,DataSpaceUsedMBMonth08 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '09', TotalSizeMBMonth09,DataSizeMBMonth09,LogSizeMBMonth09,DataSpaceUsedMBMonth09 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '10', TotalSizeMBMonth10,DataSizeMBMonth10,LogSizeMBMonth10,DataSpaceUsedMBMonth10 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '11', TotalSizeMBMonth11,DataSizeMBMonth11,LogSizeMBMonth11,DataSpaceUsedMBMonth11 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "INSERT INTO @t SELECT '12', TotalSizeMBMonth12,DataSizeMBMonth12,LogSizeMBMonth12,DataSpaceUsedMBMonth12 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                           "SELECT * FROM @t ORDER BY ByMonth DESC";

        DataSet dsChtMonth = db.ExecuteDataSet(CommandType.Text, qChtMonth);
        chtMonth.DataSource = dsChtMonth;

        chtMonth.Titles.Add("Last 12 Months");

        chtMonth.Series.Add("TotalSizeMB");
        chtMonth.Series.Add("DataSizeMB");
        chtMonth.Series.Add("LogSizeMB");
        chtMonth.Series.Add("DataSpaceUsedMB");

        chtMonth.Series["TotalSizeMB"].ChartType = SeriesChartType.Line;
        chtMonth.Series["TotalSizeMB"].BorderWidth = 2;
        chtMonth.Series["TotalSizeMB"].Color = System.Drawing.Color.Red;
        chtMonth.Series["DataSizeMB"].ChartType = SeriesChartType.Line;
        chtMonth.Series["DataSizeMB"].BorderWidth = 2;
        chtMonth.Series["DataSizeMB"].Color = System.Drawing.Color.Blue;
        chtMonth.Series["LogSizeMB"].ChartType = SeriesChartType.Line;
        chtMonth.Series["LogSizeMB"].BorderWidth = 2;
        chtMonth.Series["LogSizeMB"].Color = System.Drawing.Color.Green;
        chtMonth.Series["DataSpaceUsedMB"].ChartType = SeriesChartType.Line;
        chtMonth.Series["DataSpaceUsedMB"].BorderWidth = 2;
        chtMonth.Series["DataSpaceUsedMB"].Color = System.Drawing.Color.Purple;

        chtMonth.Series["TotalSizeMB"].XValueMember = "ByMonth";
        chtMonth.Series["TotalSizeMB"].YValueMembers = "TotalSizeMB";
        chtMonth.Series["DataSizeMB"].XValueMember = "ByMonth";
        chtMonth.Series["DataSizeMB"].YValueMembers = "DataSizeMB";
        chtMonth.Series["LogSizeMB"].XValueMember = "ByMonth";
        chtMonth.Series["LogSizeMB"].YValueMembers = "LogSizeMB";
        chtMonth.Series["DataSpaceUsedMB"].XValueMember = "ByMonth";
        chtMonth.Series["DataSpaceUsedMB"].YValueMembers = "DataSpaceUsedMB";
        chtMonth.DataBind();   
    
        //Chart Day
        string qChtDay = "DECLARE @t TABLE (ByDay VARCHAR(10),TotalSizeMB INT,DataSizeMB INT,LogSizeMB INT,DataSpaceUsedMB INT) " +
                         "INSERT INTO @t SELECT '01', TotalSizeMBDay01,DataSizeMBDay01,LogSizeMBDay01,DataSpaceUsedMBDay01 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '02', TotalSizeMBDay02,DataSizeMBDay02,LogSizeMBDay02,DataSpaceUsedMBDay02 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '03', TotalSizeMBDay03,DataSizeMBDay03,LogSizeMBDay03,DataSpaceUsedMBDay03 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '04', TotalSizeMBDay04,DataSizeMBDay04,LogSizeMBDay04,DataSpaceUsedMBDay04 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '05', TotalSizeMBDay05,DataSizeMBDay05,LogSizeMBDay05,DataSpaceUsedMBDay05 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '06', TotalSizeMBDay06,DataSizeMBDay06,LogSizeMBDay06,DataSpaceUsedMBDay06 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '07', TotalSizeMBDay07,DataSizeMBDay07,LogSizeMBDay07,DataSpaceUsedMBDay07 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '08', TotalSizeMBDay08,DataSizeMBDay08,LogSizeMBDay08,DataSpaceUsedMBDay08 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '09', TotalSizeMBDay09,DataSizeMBDay09,LogSizeMBDay09,DataSpaceUsedMBDay09 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '10', TotalSizeMBDay10,DataSizeMBDay10,LogSizeMBDay10,DataSpaceUsedMBDay10 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '11', TotalSizeMBDay11,DataSizeMBDay11,LogSizeMBDay11,DataSpaceUsedMBDay11 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '12', TotalSizeMBDay12,DataSizeMBDay12,LogSizeMBDay12,DataSpaceUsedMBDay12 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '13', TotalSizeMBDay13,DataSizeMBDay13,LogSizeMBDay13,DataSpaceUsedMBDay13 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '14', TotalSizeMBDay14,DataSizeMBDay14,LogSizeMBDay14,DataSpaceUsedMBDay14 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '15', TotalSizeMBDay15,DataSizeMBDay15,LogSizeMBDay15,DataSpaceUsedMBDay15 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '16', TotalSizeMBDay16,DataSizeMBDay16,LogSizeMBDay16,DataSpaceUsedMBDay16 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '17', TotalSizeMBDay17,DataSizeMBDay17,LogSizeMBDay17,DataSpaceUsedMBDay17 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '18', TotalSizeMBDay18,DataSizeMBDay18,LogSizeMBDay18,DataSpaceUsedMBDay18 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '19', TotalSizeMBDay19,DataSizeMBDay19,LogSizeMBDay19,DataSpaceUsedMBDay19 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '20', TotalSizeMBDay20,DataSizeMBDay20,LogSizeMBDay20,DataSpaceUsedMBDay20 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '21', TotalSizeMBDay21,DataSizeMBDay21,LogSizeMBDay21,DataSpaceUsedMBDay21 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '22', TotalSizeMBDay22,DataSizeMBDay22,LogSizeMBDay22,DataSpaceUsedMBDay22 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '23', TotalSizeMBDay23,DataSizeMBDay23,LogSizeMBDay23,DataSpaceUsedMBDay23 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '24', TotalSizeMBDay24,DataSizeMBDay24,LogSizeMBDay24,DataSpaceUsedMBDay24 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '25', TotalSizeMBDay25,DataSizeMBDay25,LogSizeMBDay25,DataSpaceUsedMBDay25 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '26', TotalSizeMBDay26,DataSizeMBDay26,LogSizeMBDay26,DataSpaceUsedMBDay26 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '27', TotalSizeMBDay27,DataSizeMBDay27,LogSizeMBDay27,DataSpaceUsedMBDay27 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '28', TotalSizeMBDay28,DataSizeMBDay28,LogSizeMBDay28,DataSpaceUsedMBDay28 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '29', TotalSizeMBDay29,DataSizeMBDay29,LogSizeMBDay29,DataSpaceUsedMBDay29 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "INSERT INTO @t SELECT '30', TotalSizeMBDay30,DataSizeMBDay30,LogSizeMBDay30,DataSpaceUsedMBDay30 FROM [Database] WHERE DatabaseName = '" + this.lblDatabaseName.Text.ToUpper().ToString() + "' and InstanceName = '" + this.lblInstanceName.Text.ToString() + "' " +
                         "SELECT * FROM @t ORDER BY ByDay DESC";

        DataSet dsChtDay = db.ExecuteDataSet(CommandType.Text, qChtDay);
        chtDay.DataSource = dsChtDay;

        chtDay.Titles.Add("Last 30 Days");

        chtDay.Legends.Add("DayLegend");
        chtDay.Legends["DayLegend"].BorderColor = System.Drawing.Color.Black;

        chtDay.Series.Add("TotalSizeMB");
        chtDay.Series.Add("DataSizeMB");
        chtDay.Series.Add("LogSizeMB");
        chtDay.Series.Add("DataSpaceUsedMB");

        chtDay.Series["TotalSizeMB"].ChartType = SeriesChartType.Line;
        chtDay.Series["TotalSizeMB"].BorderWidth = 2;
        chtDay.Series["TotalSizeMB"].Color = System.Drawing.Color.Red;
        chtDay.Series["DataSizeMB"].ChartType = SeriesChartType.Line;
        chtDay.Series["DataSizeMB"].BorderWidth = 2;
        chtDay.Series["DataSizeMB"].Color = System.Drawing.Color.Blue;
        chtDay.Series["LogSizeMB"].ChartType = SeriesChartType.Line;
        chtDay.Series["LogSizeMB"].BorderWidth = 2;
        chtDay.Series["LogSizeMB"].Color = System.Drawing.Color.Green;
        chtDay.Series["DataSpaceUsedMB"].ChartType = SeriesChartType.Line;
        chtDay.Series["DataSpaceUsedMB"].BorderWidth = 2;
        chtDay.Series["DataSpaceUsedMB"].Color = System.Drawing.Color.Purple;

        chtDay.Series["TotalSizeMB"].XValueMember = "ByDay";
        chtDay.Series["TotalSizeMB"].YValueMembers = "TotalSizeMB";
        chtDay.Series["DataSizeMB"].XValueMember = "ByDay";
        chtDay.Series["DataSizeMB"].YValueMembers = "DataSizeMB";
        chtDay.Series["LogSizeMB"].XValueMember = "ByDay";
        chtDay.Series["LogSizeMB"].YValueMembers = "LogSizeMB";
        chtDay.Series["DataSpaceUsedMB"].XValueMember = "ByDay";
        chtDay.Series["DataSpaceUsedMB"].YValueMembers = "DataSpaceUsedMB";
        chtDay.DataBind();   
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        String strTimeDisplay1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1DisplayName'").ToString();
        String strTimeDisplay2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2DisplayName'").ToString();
        String strTimeDisplay3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3DisplayName'").ToString();
        String strTimeDisplay4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4DisplayName'").ToString();

        String strTimeZone1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1TimeZoneID'").ToString();
        String strTimeZone2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2TimeZoneID'").ToString();
        String strTimeZone3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3TimeZoneID'").ToString();
        String strTimeZone4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4TimeZoneID'").ToString();

        DateTimeOffset nowDateTime = DateTimeOffset.Now;
        DateTimeOffset Time1DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone1.ToString()));
        DateTimeOffset Time2DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone2.ToString()));
        DateTimeOffset Time3DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone3.ToString()));
        DateTimeOffset Time4DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone4.ToString()));

        DbCommand dbCommand1 = db.GetSqlStringCommand("DELETE FROM [Database] WHERE InstanceName = '" + this.lblInstanceName.Text.ToString() + "' and DatabaseName = '" + this.lblDatabaseName.Text.ToString() + "'");
        DbCommand dbCommand2 = db.GetSqlStringCommand("DELETE FROM [DatabaseNote] WHERE InstanceName = '" + this.lblInstanceName.Text.ToString() + "' and DatabaseName = '" + this.lblDatabaseName.Text.ToString() + "'");
        DbCommand dbCommand3 = db.GetSqlStringCommand("DELETE FROM [DatabaseFile] WHERE InstanceName = '" + this.lblInstanceName.Text.ToString() + "' and DatabaseName = '" + this.lblDatabaseName.Text.ToString() + "'");

        db.ExecuteNonQuery(dbCommand1);
        db.ExecuteNonQuery(dbCommand2);
        db.ExecuteNonQuery(dbCommand3);

        MailMessage msg = new MailMessage();

        msg.To.Add(Utility.DBANotificationEMailString());
        msg.Subject = "DBA Alert: " + this.lblDatabaseName.Text.ToString() + " Database Deleted";
        msg.Body = "-----------------------------------------------------" + " \r\n" +
                   "       Alert   : Database Deleted From Repository" + " \r\n" +
                   "-----------------------------------------------------" + " \r\n" +
                   " SQL Instance  : " + this.lblInstanceName.Text.ToString() + " \r\n" +
                   " Database      : " + this.lblDatabaseName.Text.ToString() + " \r\n" +
                   " " + " \r\n" +
                   "-----------------------------------------------------" + " \r\n" +
                   " Actioned By   : " + Session["sFullUserName"].ToString() + " \r\n" +
                   "-----------------------------------------------------" + " \r\n" +
                   " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                   " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                   " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                   " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                   "-----------------------------------------------------" + " \r\n" +
                   "END OF MESSAGE" + " \r\n";

        SmtpClient client = new SmtpClient();
        client.Send(msg);

        Response.Redirect("~/InstanceDetail.aspx?InstanceName=" + this.lblInstanceName.Text.ToString());

    }
    protected void btnAddNote_Click(object sender, EventArgs e)
    {
        Response.Redirect("~/AddDatabaseNote.aspx?InstanceName=" + this.lblInstanceName.Text.ToString()+"&DatabaseName=" + this.lblDatabaseName.Text.ToString());
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        try
        {

            DbCommand dbCommand = db.GetSqlStringCommand("UPDATE [Database] SET" +
                                           " PrimaryContactEMail = '" + this.txtPrimaryContactEMail.Text.ToString() + "'" +
                                           ",  BackupFrequencyFULL = '" + this.txtBackupFrequencyFULL.Text.ToString() + "'" +
                                           ",  BackupFrequencyDIFF = '" + this.txtBackupFrequencyDIFF.Text.ToString() + "'" +
                                           ",  BackupFrequencyLOG = '" + this.txtBackupFrequencyLOG.Text.ToString() + "'" +
                                           ",  isAudited = '" + Convert.ToBoolean(this.chkIsAudited.Checked) + "'" +
                                           ",  isSensitive = '" + Convert.ToBoolean(this.chkIsSensitive.Checked) + "'" +
                                           ",  LastUpdated = '" + System.DateTime.Now + "'" +
                                           " WHERE InstanceName = '" + this.lblInstanceName.Text.ToString() + "' and DatabaseName = '" + this.lblDatabaseName.Text.ToString() + "'");

            db.ExecuteNonQuery(dbCommand);

            MessageBox.Show("Database Updated Successfully");

            BindData();
        }
        catch (Exception ex2)
        {
            MessageBox.Show("Update Failed: " + ex2.Message.ToString());
            return;
        }
    }        
    protected void chkAllInstanceDatabaseNotes_CheckedChanged(object sender, EventArgs e)
    {
        BindData();
    }
}
